package net.tngou.iask.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.tngou.iask.jdbc.PageUtil;
import net.tngou.iask.jdbc.QueryHelper;
import net.tngou.iask.pojo.Answer;
import net.tngou.iask.pojo.Disease;
import net.tngou.iask.pojo.Filter;
import net.tngou.iask.pojo.POJO;
import net.tngou.iask.pojo.Question;
import net.tngou.iask.pojo.Treatment;
import net.tngou.iask.util.StringUtil;

/**
 * 问题
 * @author tngou@tngou.net
 *
 */
public class QuestionDao extends BaseDao {

	
	/**
	 * 取得问题
	 * @param page 当前页
	 * @param size 页大小
	 * @param main 实体（）
	 * @param type 类型
	 * @param keyword 搜索关键词
	 * @return
	 */
	public PageUtil getPage(int page,int size,long main,long type,String keyword) {
		List<Question> list=new ArrayList<>();
		int total=0;
		String sql_r="SELECT *";
		String sql_t="SELECT COUNT(id) ";
		String sql_o=" ORDER BY id DESC";//排序
		String sql=" FROM `"+POJO.DABASE+"_question`";
		if(main!=0)sql=sql+ " WHERE  main IN(0,?) AND type=?";
		else sql=sql+ " WHERE  main=? AND type=?";
		if(StringUtil.isNotEmpty(keyword))
		{
			sql=sql+" AND (name LIKE ? OR title  LIKE ? OR keyword  LIKE ?)";
			
			Object[] params={main,type,"%"+keyword+"%","%"+keyword+"%","%"+keyword+"%"};
			list=QueryHelper.query_slice(Question.class, sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}else{
			Object[] params={main,type};
			list=QueryHelper.query_slice(Question.class, sql_r+sql+sql_o, page, size, params);	
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}		
		return new PageUtil(list, page, size, total);
		
	}
	
	/**
	 * 
	 * @param page
	 * @param size
	 * @param main
	 * @param keyword
	 * @return
	 */
	public PageUtil getPage(int page,int size,long main,String keyword) {
		List<Question> list=new ArrayList<>();
		int total=0;
		String sql_r="SELECT *";
		String sql_t="SELECT COUNT(id) ";
		String sql_o=" ORDER BY id DESC";//排序
		String sql=" FROM `"+POJO.DABASE+"_question`";
		if(main!=0)sql=sql+ " WHERE  main IN(0,?) AND type <>4";
		else sql=sql+ " WHERE  main=?   AND type <>4 ";
		if(StringUtil.isNotEmpty(keyword))
		{
			sql=sql+" AND (name LIKE ? OR title  LIKE ? OR keyword  LIKE ?)";
			
			Object[] params={main,"%"+keyword+"%","%"+keyword+"%","%"+keyword+"%"};
			list=QueryHelper.query_slice(Question.class, sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}else{
			Object[] params={main};
			list=QueryHelper.query_slice(Question.class, sql_r+sql+sql_o, page, size, params);	
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}		
		return new PageUtil(list, page, size, total);
		
	}
	/**
	 * 取得问题列表
	 * @param main
	 * @param type
	 * @return
	 */
	public List<Answer> getList(long main,long type) {
		String sql_r="SELECT * ";
		String sql_o=" ORDER BY id DESC";//排序
		String sql=" FROM `"+POJO.DABASE+"_question`";
		if(main!=0)sql=sql+ " WHERE  main IN(0,?) AND type=?";
		else sql=sql+ " WHERE  main=? AND type=?";
		Object[] params={main,type};
		return  QueryHelper.query(Answer.class, sql_r+sql+sql_o, params);
	}
	
	/**
	 * 更新问题中间库
	 * @param question 问题ID
	 * @param answers 选项
	 * @param filters 过滤条件
	 */
	public void update(long question,Long[] filters) {
		
		//问题 对应过滤条件
		
		if(filters!=null)
		{
			String delete_sql="DELETE FROM `"+POJO.DABASE+"_question_filter` WHERE question=?";
			String add_sql ="INSERT INTO `"+POJO.DABASE+"_question_filter`( `question`, `filter`) VALUES (?,?)";
			QueryHelper.update(delete_sql, question);
			Object[][] params =new Object[filters.length][2];
			for (int i = 0; i < filters.length; i++) {
				params[i][0]=question;
				params[i][1]=filters[i];	
			}
			QueryHelper.batch(add_sql, params );
		}
		
	}
	
	/**
	 * 取得问题对应的 选项
	 * @param question
	 * @return
	 */
	public List<Answer> getAnswer(long question) {
		String sql="SELECT answer.*,question_answer.seq as seq FROM `"+POJO.DABASE+"_answer` answer,`"+POJO.DABASE+"_question_answer` question_answer WHERE answer.id=question_answer.answer AND question_answer.question=? ORDER BY seq ASC";
		return QueryHelper.query(Answer.class, sql, question);	
	}
	
	/**
	 * 取得问题没有被选择的问题
	 * @param question
	 * @param type
	 * @return
	 */
	public List<Answer> getNotSelectAnswer(long question,int type) {
		String sql ="SELECT * FROM `"+POJO.DABASE+"_answer` WHERE type=? AND id NOT IN (SELECT answer FROM `"+POJO.DABASE+"_question_answer`  WHERE question=?)";
		return QueryHelper.query(Answer.class, sql,type, question);
		
	}
	
	/**
	 * 取得问题选项
	 * @param page 当前页
	 * @param size 页大小
	 * @param main 实体（）
	 * @param type 类型
	 * @param keyword 搜索关键词
	 * @return
	 */
	public PageUtil getPage(int page,int size,long main,long question,long type,String keyword) {
		List<Answer> list=new ArrayList<>();
		int total=0;
		String sql_r="SELECT *";
		String sql_t="SELECT COUNT(id) ";
		String sql_o=" ORDER BY main DESC,id DESC ";//排序
		String sql=" FROM `"+POJO.DABASE+"_answer`";
		if(main!=0)sql=sql+ " WHERE  main IN(0,?) AND type=? AND id NOT IN (SELECT answer FROM `"+POJO.DABASE+"_question_answer`  WHERE question=?)";
		else sql=sql+ " WHERE  main=? AND type=?  AND id NOT IN (SELECT answer FROM `"+POJO.DABASE+"_question_answer`  WHERE question=?)";
		if(StringUtil.isNotEmpty(keyword))
		{
			sql=sql+" AND name LIKE ?";
			
			Object[] params={main,type,question,"%"+keyword+"%"};
			list=QueryHelper.query_slice(Answer.class, sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}else{
			Object[] params={main,type,question};
			list=QueryHelper.query_slice(Answer.class, sql_r+sql+sql_o, page, size, params);	
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}		
		return new PageUtil(list, page, size, total);
		
	}
	
	/**
	 * 取得问题对应的 选项
	 * @param question
	 * @return
	 */
	public List<Map<String, Object>> getQuestionAnswer(long question) {
		String sql="SELECT answer.name,answer.value,question_answer.id,question_answer.seq FROM `"+POJO.DABASE+"_answer` answer,`"+POJO.DABASE+"_question_answer` question_answer WHERE answer.id=question_answer.answer AND question_answer.question=? ORDER BY seq ASC";
		return QueryHelper.query(sql, question);	
	}
	
	/**
	 * 取得问题列表
	 * @param main 实体（）
	 * @return
	 */
	public List<Question>  getList(long main) {
		String sql_r="SELECT *";
		String sql_o=" ORDER BY id ASC";//排序
		String sql=" FROM `"+POJO.DABASE+"_question`";
		if(main!=0)sql=sql+ " WHERE  main IN(0,?)";
		else sql=sql+ " WHERE  main=?";
		Object[] params={main};
		return QueryHelper.query(Question.class,sql_r+sql+sql_o,params);
	}
	
	/**
	 * 取得没有实体
	 * @param main
	 * @param survey
	 * @return
	 */
	public List<Question>  getListNotSurvey(long main,long survey) {
		String sql_r="SELECT *";
		String sql_o=" ORDER BY id ASC";//排序
		String sql=" FROM `"+POJO.DABASE+"_question`";
		if(main!=0)sql=sql+ " WHERE  main IN(0,?)";
		else sql=sql+ " WHERE  main=?";
		sql =sql +" AND id NOT IN(SELECT question FROM `"+POJO.DABASE+"_survey_question` WHERE survey=? )";
		Object[] params={main,survey};
		return QueryHelper.query(Question.class,sql_r+sql+sql_o,params);
	}
	
	public PageUtil getPageNotQuestion(int page,int size,long main,long survey,String keyword) {
		List<Question> list=new ArrayList<>();
		int total=0;
		String sql_r="SELECT *";
		String sql_t="SELECT COUNT(id) ";		
		String sql_o=" ORDER BY id ASC";//排序
		String sql=" FROM `"+POJO.DABASE+"_question`";
		if(main!=0)sql=sql+ " WHERE  main IN(0,?)";
		else sql=sql+ " WHERE  main=?";
		sql =sql +" AND id NOT IN(SELECT question FROM `"+POJO.DABASE+"_survey_question` WHERE survey=? )";
		if(StringUtil.isNotEmpty(keyword))
		{
			sql=sql+" AND (name LIKE ? OR title  LIKE ? )";
			
			Object[] params={main,survey,"%"+keyword+"%","%"+keyword+"%"};
			list=QueryHelper.query_slice(Question.class, sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}else{
			Object[] params={main,survey};
			list=QueryHelper.query_slice(Question.class, sql_r+sql+sql_o, page, size, params);	
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}		
		return new PageUtil(list, page, size, total);
	}
	
	
	/**
	 * 取得问卷对应的问题
	 * @param survey
	 * @return
	 */
	public List<Question> getQuestion(long survey) {
		String sql="SELECT question.*,survey_question.seq  FROM `"+POJO.DABASE+"_question` question,`"+POJO.DABASE+"_survey_question` survey_question WHERE question.id=survey_question.question AND survey_question.survey=? ORDER BY seq ASC";
		return QueryHelper.query(Question.class, sql, survey);	
	}
	public List<Map<String, Object>> getQuestionMap(long survey) {
		String sql="SELECT question.name,question.type,question.title,question.tag,survey_question.id,survey_question.question,survey_question.seq FROM `"+POJO.DABASE+"_question` question,`"+POJO.DABASE+"_survey_question` survey_question WHERE question.id=survey_question.question AND survey_question.survey=? ORDER BY seq ASC";
		return QueryHelper.query( sql, survey);	
	}
	
	
	/**
	 * 取得问题对应过滤条件
	 * @param question
	 * @return
	 */
	public List<Filter> getFilter(long question) {		
		String sql ="SELECT ft.* FROM `"+POJO.DABASE+"_filter` ft ,`"+POJO.DABASE+"_question_filter` question_filter WHERE question_filter.filter=ft.id AND question_filter.question=? ";
		return QueryHelper.query(Filter.class, sql, question);
	}
	
	
	
	
}
